﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace MoneyClass
{
    public class Expenses
    {
        /// <summary>
        /// 获取所有收支记录
        /// </summary>
        /// <returns>DataTable</returns>
        public static DataTable getAll()
        {
            string sql = "select A.[GUID],iif(a.[type]=0,\"收入\",\"支出\") as typeText,a.[type],b.[Ident],a.[bid],c.[Classify],a.[cid],a.[thisDate],a.[money],a.[comment] from ([QT_User_Expenses] a left join [QT_User_Bank] b on a.[bid]=b.[guid]) left join [QT_User_Classify] c on a.[cid]=c.[guid] order by a.[thisDate] desc,a.[uid] asc";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connectionString).DataTable(sql);
            return dt;
        }

        /// <summary>
        /// 获取指定用户的所有收支记录
        /// </summary>
        /// <param name="uid">用户ID</param>
        /// <returns>DataTable</returns>
        public static DataTable get(string uid)
        {
            string sql = "select top 100 A.[GUID],iif(a.[type]=0,\"收入\",\"支出\") as typeText,a.[type],b.[Ident],a.[bid],c.[Classify],a.[cid],a.[thisDate],a.[money],a.[Comment] from ([QT_User_Expenses] a left join [QT_User_Bank] b on a.[bid]=b.[guid]) left join [QT_User_Classify] c on a.[cid]=c.[guid] where a.[uid]='" + uid + "' order by a.[thisDate] desc";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connectionString).DataTable(sql);
            return dt;
        }

        /// <summary>
        /// 获取指定用户一段时间的收支记录
        /// </summary>
        /// <param name="uid">用户ID</param>
        /// <param name="type">收支类型</param>
        /// <param name="sdate">开始时间</param>
        /// <param name="edate">结束时间</param>
        /// <returns>DataTable</returns>
        public static DataTable get(string uid,List<string> type,string sdate,string edate)
        {
            string sql = "select A.[GUID],iif(a.[type]=0,\"收入\",\"支出\") as typeText,a.[type],b.[Ident],a.[bid],c.[Classify],a.[cid],a.[thisDate],a.[money],a.[comment] from ([QT_User_Expenses] a left join [QT_User_Bank] b on a.[bid]=b.[guid]) left join [QT_User_Classify] c on a.[cid]=c.[guid] where a.[uid]='" + uid + "' and a.[type] in(" + string.Join(",", type.ToArray()) + ") and a.[thisDate]>=#" + sdate + "# and a.[thisDate]<#" + edate + "# order by a.[thisDate] desc";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connectionString).DataTable(sql);
            return dt;
        }

        /// <summary>
        /// 获取指定用户某一段时间的收支统计
        /// </summary>
        /// <param name="uid">用户ID</param>
        /// <param name="type">收支类型</param>
        /// <param name="cid">分类ID</param>
        /// <param name="sdate">起始时间</param>
        /// <param name="edate">结束时间</param>
        /// <param name="dtype">统计类型。y：年；m：月；d：天</param>
        /// <returns>DataTable</returns>
        public static DataTable get(string uid, string type, string cid, string sdate, string edate, string dtype)
        {
            string sql = "select mydate,sum(money) from (select format([thisDate],\"";
            switch (dtype.ToLower())
            {
                case "y":
                    sql += "yyyy-01-01";
                    break;
                case "m":
                    sql += "yyyy-MM-01";
                    break;
                default:
                    sql += "yyyy-MM-dd";
                    break;
            }

            sql += "\") as mydate,[money] from [QT_User_Expenses] where [uid]='" + uid + "' and [type]=" + type + (string.IsNullOrEmpty(cid) ? "" : (" and [cid]='" + cid + "'")) + " and [item]>=0 and [thisDate]>=#" + sdate + "# and [thisDate]<#" + edate + "#) group by mydate";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connectionString).DataTable(sql);
            return dt;
        }

        /// <summary>
        /// 获取指定用户收支总计
        /// </summary>
        /// <param name="uid">用户ID</param>
        /// <param name="type">收支类型</param>
        /// <returns>float</returns>
        public static float getCount(string uid, string type)
        {
            string sql = "select sum([money]) from [QT_User_Expenses] where [uid]='" + uid + "' and [type]=" + type + " and [item]>=0";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connectionString).DataTable(sql);
            return float.Parse(string.IsNullOrEmpty(dt.Rows[0][0].ToString()) ? "0" : dt.Rows[0][0].ToString());
        }

        /// <summary>
        /// 获取指定用户某一段时间的收支总计
        /// </summary>
        /// <param name="uid">用户ID</param>
        /// <param name="type">收支类型</param>
        /// <param name="sdate">起始时间</param>
        /// <param name="edate">结束时间</param>
        /// <returns>float</returns>
        public static float getCount(string uid, string type, string sdate, string edate)
        {
            string sql = "select sum([money]) from [QT_User_Expenses] where [uid]='" + uid + "' and [type]=" + type + " and [item]>=0 and [thisDate]>=#" + sdate + "# and [thisDate]<#" + edate + "#";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connectionString).DataTable(sql);
            return float.Parse(string.IsNullOrEmpty(dt.Rows[0][0].ToString()) ? "0" : dt.Rows[0][0].ToString());
        }

        /// <summary>
        /// 获取指定用户某一分类的某一段时间的收支总结
        /// </summary>
        /// <param name="uid">用户ID</param>
        /// <param name="type">收支类型</param>
        /// <param name="item">分类</param>
        /// <param name="sdate">起始时间</param>
        /// <param name="edate">结束时间</param>
        /// <returns>float</returns>
        public static float getCount(string uid, string type, string item, string sdate, string edate)
        {
            string sql = "select sum([money]) from [QT_User_Expenses] where [uid]='" + uid + "' and [type]=" + type + " and [item]=" + item + " and [thisDate]>=#" + sdate + "# and [thisDate]<#" + edate + "#";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connectionString).DataTable(sql);
            return float.Parse(string.IsNullOrEmpty(dt.Rows[0][0].ToString()) ? "0" : dt.Rows[0][0].ToString());
        }

        /// <summary>
        /// 获取指定用户收支记录的时间段
        /// </summary>
        /// <param name="uid">用户ID</param>
        /// <returns>ExpensesPeriod</returns>
        public static ExpensesPeriod getPeriod(string uid)
        {
            string sql = "select [thisDate] from [QT_User_Expenses] where [uid]='" + uid + "' order by [thisDate] asc";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connectionString).DataTable(sql);
            ExpensesPeriod rVal = new ExpensesPeriod();
            if (dt.Rows.Count > 0)
            {
                rVal.Start = DateTime.Parse(dt.Rows[0][0].ToString()).ToString("yyyy年MM月dd日");
                rVal.End = DateTime.Parse(dt.Rows[dt.Rows.Count - 1][0].ToString()).ToString("yyyy年MM月dd日");
            }
            else
            {
                rVal.Start = DateTime.Now.ToString("yyyy年MM月dd日");
                rVal.End = DateTime.Now.ToString("yyyy年MM月dd日");
            }
            return rVal;
        }

        /// <summary>
        /// 添加一条收支记录
        /// </summary>
        /// <param name="uid">用户ID</param>
        /// <param name="type">收支类型</param>
        /// <param name="bid">银行ID</param>
        /// <param name="cid">分类ID</param>
        /// <param name="date">日期</param>
        /// <param name="money">金额</param>
        /// <returns>int</returns>
        public static int Add(string uid, string type, string item, string bid, string cid, string date, string money,string comment)
        {
            string sql = "insert into [QT_User_Expenses]([guid],[uid],[type],[item],[bid],[cid],[thisDate],[money],[comment]) values('" + Guid.NewGuid().ToString().Replace("-", "") + "','" + uid + "'," + type + ",'" + item + "','" + bid + "','" + cid + "','" + date + "'," + money + ",'" + comment + "')";
            int rval = new QT.DBO.OleDBHelper(myDB.connectionString).ExecuteNonQuery(sql);
            return rval;
        }

        /// <summary>
        /// 删除收支记录
        /// </summary>
        /// <param name="guid">Guid</param>
        /// <returns>int</returns>
        public static int Delete(string guid)
        {
            List<string> guids = new List<string>();
            guids.Add(guid);
            return Delete(guids);
        }

        /// <summary>
        /// 批量删除收支记录
        /// </summary>
        /// <param name="guids">一组Guid</param>
        /// <returns>int</returns>
        public static int Delete(List<string> guids)
        {
            string sql = "delete from [QT_User_Expenses] where [guid] in('" + string.Join("','", guids.ToArray()) + "')";
            int rval = new QT.DBO.OleDBHelper(myDB.connectionString).ExecuteNonQuery(sql);
            return rval;
        }
    }

    public struct ExpensesPeriod
    {
        /// <summary>
        /// 开始时间
        /// </summary>
        public string Start;
        /// <summary>
        /// 结束时间
        /// </summary>
        public string End;
    }
}
